package com.miracle.utils;

import com.ruoyi.common.utils.RegexUtil;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;
import com.miracle.flexAPI.api.entity.SysTask;
import com.miracle.flexAPI.api.entity.SysTaskColumn;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;

/**
 * 任务配置执行工具类
 */

@Slf4j
public class DatabaseToolTaskManage implements DatabaseTool {

    public static final String SQL_PARAM_MATCH_REGULAR_1 = "(?<=\\{).*?(?=\\})";// 匹配｛｝，不包含｛｝
    public static final String SQL_PARAM_MATCH_REGULAR_2 = "(\\{).*?(\\})";// 匹配｛｝，包含｛｝
    public static final String SQL_PREPARE_SIGN = "?";// 预编译占位符

    private DataSource dataSource;



    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet;


    /**
     * @param dataSource 数据源
     */
    public DatabaseToolTaskManage(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 执行SQL语句
     * @param sqlString 任务配置实体
     * @param params 参数实体
     * @return
     */
    public <T extends Map> DatabaseToolTaskManage executeSql(String sqlString, T params) throws SQLException, IllegalAccessException {
        //sqlString = "select * from sys_user where VC_USERNAME = {P1}";
        //params.setP1("系统管理员");
        //params.setP2("curd");
        //params.setP3("138");
        //params.setP7("24");
        //params.setP14("cny");
        if(StringUtils.isBlank(sqlString)){
            throw new NullPointerException("sqlString is null");
        }

        //1.提取SQL语句中的参数集，将SQL语句编译为预编译SQL
        List<String> paramNameList = RegexUtil.matchAll(SQL_PARAM_MATCH_REGULAR_1, sqlString);
        String newSqlString = sqlString.replaceAll(SQL_PARAM_MATCH_REGULAR_2, SQL_PREPARE_SIGN);
        log.debug("taskSQL:\n" + newSqlString);

        //todo 尝试使用 JdbcTemplate 执行任务
        //JdbcTemplate jdbcTemplate = null;
        //jdbcTemplate.execute(newSqlString, new PreparedStatementCallback<Object>() {
        //    @Override
        //    public Object doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
        //        return null;
        //    }
        //});

        this.connection = dataSource.getConnection();
        //2.执行预编译SQL
        preparedStatement = this.connection.prepareStatement(newSqlString);

        //3.为预编译对象赋值
        List<Object> paramValList = new ArrayList<>();
        for (int i = 0; i < paramNameList.size(); i++) {
            String paramName = paramNameList.get(i);
            Object param = params.get(paramName);
            paramValList.add(param);
            preparedStatement.setObject(i + 1, param);
        }
        log.debug("taskParams: " + paramNameList);
        log.debug("taskValues: " + paramValList);
        //4.执行预编译SQL
        this.resultSet = preparedStatement.executeQuery();

        return this;
    }


    /**
     * 获取SQL执行结果
     * @return
     * @throws SQLException
     */
    public <T extends Map> List<T> getSqlResult() throws SQLException {
        List<T> dataList = new ArrayList<>();
        //5.解析执行结果
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            T rowData = (T) new LinkedHashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
            dataList.add(rowData);
        }
        log.debug("taskDataSize: " + dataList.size());
        return dataList;
    }


    /**
     * 获取SQL执行结果
     * @return
     * @throws SQLException
     */
    public <T> List<T> getSqlResult(Class<T> clazz) throws SQLException, IllegalAccessException, InstantiationException {
        List<T> dataList = new ArrayList<>();
        //5.解析执行结果
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()) {
            if(clazz.isInterface() || Modifier.isAbstract(clazz.getModifiers())){
                throw new RuntimeException(clazz.toString() + "could not create a new instance");
            }
            T rowData = clazz.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                ReflectUtils.invokeSetter(rowData, metaData.getColumnName(i), resultSet.getObject(i));
            }
            dataList.add(rowData);
        }
        return dataList;
    }


    /**
     * 获取表头信息
     * @return
     * @throws SQLException
     */
    public List<SysTaskColumn> getSqlColumnList(String vcTaskid) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        List<SysTaskColumn> newColElement = createColumnList(metaData, vcTaskid);
        return newColElement;
    }


    /**
     * 根据属性名获取属性值
     *
     * @param fieldName
     * @param object
     * @return
     */
    private static String getFieldValueByFieldName(String fieldName, Object object) {
        try {
            Field field = object.getClass().getDeclaredField(fieldName);
            //设置对象的访问权限，保证对private的属性的访问
            field.setAccessible(true);
            return  (String)field.get(object);
        } catch (Exception e) {

            return null;
        }
    }


    /**
     * 生成表头信息
     * @param metaData
     * @param vcTaskid
     * @return
     * @throws SQLException
     */
    private List<SysTaskColumn> createColumnList(ResultSetMetaData metaData, String vcTaskid) throws SQLException {
        List<SysTaskColumn> ColElementList = new ArrayList();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            String dataType ="0";
            String textAlign = "2";
            if (metaData.getColumnTypeName(i).equals("NUMBER")){
                dataType = "1";
                textAlign = "0";
            }else if(metaData.getColumnTypeName(i).equals("DATE")){
                dataType = "2";
            }
            //取最大+1
            SysTaskColumn stc = new SysTaskColumn(vcTaskid,metaData.getColumnName(i),metaData.getColumnName(i)
                    ,dataType,"1",120,textAlign,"","",i);
            ColElementList.add(stc);
        }
        return ColElementList;

    }

    public void close() throws SQLException {
        if (connection != null) connection.close();
        if (preparedStatement != null) preparedStatement.close();
        if (resultSet != null) resultSet.close();
    }

    public static void main(String[] args) throws SQLException {
        DatabaseToolTaskManage taskFactoryTools = null;
        try {
            taskFactoryTools = new DatabaseToolTaskManage(null)
                    .executeSql(new SysTask().getTaskSql(), new HashMap<>());
            //获取表头信息
            List<SysTaskColumn> sqlColumnList = taskFactoryTools.getSqlColumnList(new SysTask().getTaskSql());
            //获取执行结果
            List<Map<String, Object>> sqlResult = taskFactoryTools.getSqlResult();
        } catch (SQLException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            taskFactoryTools.close();
        }
    }
}
